{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task\n",
    "\n",
    "We use the Penn World Tables and OECD data to produce estimates of $(\\mu,\\alpha,\\delta_k)$ as input in the model (auxiliary parameters). This yields Table 6 in the paper as well as a csv file that collects parameters for the model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:35:14.459165Z",
     "start_time": "2019-05-23T21:35:14.455573Z"
    }
   },
   "outputs": [],
   "source": [
    "from matplotlib import pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = ['us','fr','it','dk','sp','nl','se','de']\n",
    "cnames = ['United States','France','Italy','Denmark','Spain','Netherlands','Sweden','Germany']\n",
    "co = dict(zip(countries,cnames))\n",
    "ce = dict(zip(cnames,countries))\n",
    "data = '../data_sources/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load the Penn World Table for some of the stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:35:17.719231Z",
     "start_time": "2019-05-23T21:35:15.146168Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "years = range(1995,2016,1)\n",
    "penn = pd.read_excel(data+'pwt/pwt90.xlsx',sheet_name='Data')\n",
    "penn = penn[(penn['year'].isin(years)) & (penn['country'].isin(cnames))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load OECD data for co-insurance rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:36:00.259124Z",
     "start_time": "2019-05-23T21:36:00.029421Z"
    }
   },
   "outputs": [],
   "source": [
    "labels = ['country','unit','scrap']\n",
    "for t in range(1995,2016):\n",
    "    labels.append(str(t))\n",
    "oecd = pd.read_excel(data+'oecd/oecd-copays-1995-2015.xls',skiprows=1)\n",
    "oecd.columns = labels\n",
    "oecd = oecd.drop(columns=['scrap','unit'])\n",
    "oecd = oecd.set_index('country')\n",
    "oecd = oecd.stack().to_frame()\n",
    "oecd.columns = ['copay']\n",
    "oecd['copay'] = oecd['copay']/100.0\n",
    "oecd.index.names = ['country','year']\n",
    "oecd = oecd.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:36:19.099091Z",
     "start_time": "2019-05-23T21:36:19.094872Z"
    }
   },
   "source": [
    "# Co-insurance rates (generosity)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We take from OECD average out-of-pocket health expenditures as a fraction of total. We compute the mean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:36:46.379293Z",
     "start_time": "2019-05-23T21:36:46.363901Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>copay</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>0.149318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>0.085540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>0.127620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>0.237656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>0.097602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>0.228510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>0.162742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>0.136411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  copay\n",
       "country                \n",
       "Denmark        0.149318\n",
       "France         0.085540\n",
       "Germany        0.127620\n",
       "Italy          0.237656\n",
       "Netherlands    0.097602\n",
       "Spain          0.228510\n",
       "Sweden         0.162742\n",
       "United States  0.136411"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "copays = oecd['copay'].groupby(oecd['country']).mean().to_frame()\n",
    "copays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Depreciation Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:37:15.877349Z",
     "start_time": "2019-05-23T21:37:15.867464Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>delta</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>0.043378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>0.040203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>0.039408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>0.039708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>0.041134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>0.037669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>0.046767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>0.048735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  delta\n",
       "country                \n",
       "Denmark        0.043378\n",
       "France         0.040203\n",
       "Germany        0.039408\n",
       "Italy          0.039708\n",
       "Netherlands    0.041134\n",
       "Spain          0.037669\n",
       "Sweden         0.046767\n",
       "United States  0.048735"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delta = penn['delta'].groupby(penn['country']).mean().to_frame()\n",
    "delta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Labor Share"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We take the labor share from Penn World Tables. We compute the mean over the period 1995-2015."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:37:41.718429Z",
     "start_time": "2019-05-23T21:37:41.712655Z"
    }
   },
   "outputs": [],
   "source": [
    "alpha = penn['labsh'].groupby(penn['country']).mean().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:37:47.535320Z",
     "start_time": "2019-05-23T21:37:47.532096Z"
    }
   },
   "outputs": [],
   "source": [
    "alpha['labsh'] = 1-alpha['labsh']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:37:53.521551Z",
     "start_time": "2019-05-23T21:37:53.516176Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>alpha</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>0.360473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>0.379034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>0.372822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>0.470268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>0.393142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>0.373838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>0.461041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>0.383702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  alpha\n",
       "country                \n",
       "Denmark        0.360473\n",
       "France         0.379034\n",
       "Germany        0.372822\n",
       "Italy          0.470268\n",
       "Netherlands    0.393142\n",
       "Spain          0.373838\n",
       "Sweden         0.461041\n",
       "United States  0.383702"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alpha.columns = ['alpha']\n",
    "alpha"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Production of Table 6\n",
    "\n",
    "This is Table 6 in paper. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:39:02.233866Z",
     "start_time": "2019-05-23T21:39:02.231473Z"
    }
   },
   "outputs": [],
   "source": [
    "table = copays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:39:08.626266Z",
     "start_time": "2019-05-23T21:39:08.621260Z"
    }
   },
   "outputs": [],
   "source": [
    "for x in [alpha,delta]:\n",
    "    table = table.merge(x,left_index=True,right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-23T21:39:18.966382Z",
     "start_time": "2019-05-23T21:39:18.955116Z"
    }
   },
   "outputs": [],
   "source": [
    "table = table[['copay','alpha','delta']]\n",
    "table.columns = ['mu','alpha','delta']\n",
    "table = table.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in table.columns:\n",
    "    table[c] = table[c].astype('float64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>country</th>\n",
       "      <th>Denmark</th>\n",
       "      <th>France</th>\n",
       "      <th>Germany</th>\n",
       "      <th>Italy</th>\n",
       "      <th>Netherlands</th>\n",
       "      <th>Spain</th>\n",
       "      <th>Sweden</th>\n",
       "      <th>United States</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mu</th>\n",
       "      <td>0.149</td>\n",
       "      <td>0.086</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.238</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.229</td>\n",
       "      <td>0.163</td>\n",
       "      <td>0.136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alpha</th>\n",
       "      <td>0.360</td>\n",
       "      <td>0.379</td>\n",
       "      <td>0.373</td>\n",
       "      <td>0.470</td>\n",
       "      <td>0.393</td>\n",
       "      <td>0.374</td>\n",
       "      <td>0.461</td>\n",
       "      <td>0.384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta</th>\n",
       "      <td>0.043</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.039</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.047</td>\n",
       "      <td>0.049</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "country  Denmark  France  Germany  Italy  Netherlands  Spain  Sweden  \\\n",
       "mu         0.149   0.086    0.128  0.238        0.098  0.229   0.163   \n",
       "alpha      0.360   0.379    0.373  0.470        0.393  0.374   0.461   \n",
       "delta      0.043   0.040    0.039  0.040        0.041  0.038   0.047   \n",
       "\n",
       "country  United States  \n",
       "mu               0.136  \n",
       "alpha            0.384  \n",
       "delta            0.049  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.round(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.loc[:,['Germany','Denmark','France','Italy','Netherlands','Sweden','Spain','United States']].round(3).to_latex('../tables/table_6_auxiliary.tex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving Auxiliary Parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We save the parameters to file fot the model to load. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "newcolumns = table.columns.to_list()\n",
    "for i,c in enumerate(table.columns):\n",
    "    newcolumns[i] = ce[c].upper()\n",
    "table.columns = newcolumns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DK</th>\n",
       "      <th>FR</th>\n",
       "      <th>DE</th>\n",
       "      <th>IT</th>\n",
       "      <th>NL</th>\n",
       "      <th>SP</th>\n",
       "      <th>SE</th>\n",
       "      <th>US</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mu</th>\n",
       "      <td>0.149318</td>\n",
       "      <td>0.085540</td>\n",
       "      <td>0.127620</td>\n",
       "      <td>0.237656</td>\n",
       "      <td>0.097602</td>\n",
       "      <td>0.228510</td>\n",
       "      <td>0.162742</td>\n",
       "      <td>0.136411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alpha</th>\n",
       "      <td>0.360473</td>\n",
       "      <td>0.379034</td>\n",
       "      <td>0.372822</td>\n",
       "      <td>0.470268</td>\n",
       "      <td>0.393142</td>\n",
       "      <td>0.373838</td>\n",
       "      <td>0.461041</td>\n",
       "      <td>0.383702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta</th>\n",
       "      <td>0.043378</td>\n",
       "      <td>0.040203</td>\n",
       "      <td>0.039408</td>\n",
       "      <td>0.039708</td>\n",
       "      <td>0.041134</td>\n",
       "      <td>0.037669</td>\n",
       "      <td>0.046767</td>\n",
       "      <td>0.048735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DK        FR        DE        IT        NL        SP        SE  \\\n",
       "mu     0.149318  0.085540  0.127620  0.237656  0.097602  0.228510  0.162742   \n",
       "alpha  0.360473  0.379034  0.372822  0.470268  0.393142  0.373838  0.461041   \n",
       "delta  0.043378  0.040203  0.039408  0.039708  0.041134  0.037669  0.046767   \n",
       "\n",
       "             US  \n",
       "mu     0.136411  \n",
       "alpha  0.383702  \n",
       "delta  0.048735  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.to_pickle('../model/params/auxiliary.pkl')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "cf2a50979671a58939829e6829efb726aa5da11149213b77bd50351f899d04fb"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
